from src.main.server.mapper.CreateMysqlCon import CreateMysqlCon


class ServerReviewImpl:

    def __init__(self):
        self.con = CreateMysqlCon().get_con()
        if not self.con:
            raise ConnectionError("数据库连接失败，请检查配置或网络")
        self.cursor = self.con.cursor()

    def all_info(self):
        """查询所有服务记录信息"""
        SQL = ("""
               SELECT sp.server_name, v.username, sr.apply_time, sr.review_time, sr.status 
               FROM volunteers_info as v, server_project as sp, server_review as sr 
               WHERE sr.vol_id = v.ID and sr.server_id = sp.ID
               """)

        self.cursor.execute(SQL)
        return self.cursor.fetchall()

    def get_info(self, fd, sd):
        SQL = ("""
               SELECT sp.server_name AS server_name,
                      v.username     AS username,
                      sr.apply_time  AS apply_time,
                      sr.status      AS status
               FROM server_review sr
                        JOIN server_project sp ON sr.server_id = sp.ID
                        JOIN volunteers_info v ON sr.vol_id = v.ID
               WHERE sp.server_name = %s
                 AND v.username = %s
               """)
        self.cursor.execute(SQL, (fd, sd))
        return self.cursor.fetchall()

    def update(self, data):
        SQL = ("""
                update server_review sr
                        JOIN server_project sp ON sr.server_id = sp.ID
                        JOIN volunteers_info v ON sr.vol_id = v.ID
                set status = %s, review_time = %s
                where sp.server_name = %s and v.username = %s  
                """)
        self.cursor.execute(SQL, data)

    def insert(self, data):
        SQL = ("""
               INSERT INTO server_review (server_id, vol_id, apply_time)
                SELECT sp.ID, v.ID, %s
                FROM server_project sp
                JOIN volunteers_info v ON sp.server_name = %s AND v.username = %s
            """)
        self.cursor.execute(SQL, data)

    def check(self,data):
        """确认数据库是否已经存在该数据
           保证唯一性
        """
        SQL = """
                SELECT count(*)
               FROM server_review sr
                        JOIN server_project sp ON sr.server_id = sp.ID
                        JOIN volunteers_info v ON sr.vol_id = v.ID
               WHERE sp.server_name = %s
                 AND v.username = %s
              """
        self.cursor.execute(SQL, data)
        return self.cursor.fetchone()

    def delete(self, data):
        SQL = """
            DELETE sr
                FROM server_review sr
                    JOIN server_project sp ON sr.server_id = sp.ID
                    JOIN volunteers_info v ON sr.vol_id = v.ID
                WHERE sp.server_name = %s AND v.username = %s
              """
        self.cursor.execute(SQL, data)


if __name__ == '__main__':
    data = ServerReviewImpl().check(("送外卖", "张三"))
    print(data)